<?php
namespace app\api\controller\basemanage;
use app\api\controller\common\ApiBase;
use Think\Db; 
use app\api\controller\common\CommonPlus;
use PHPExcel_IOFactory;
use PHPExcel;
use app\api\model\Admin as AdminModel;
use think\Controller; // dengh quxiao
// Controller  ApiBase （dengl）
class Publication extends ApiBase
{
    public function _initialize()
    {
        parent::_initialize();
        $this->admin = session('admin');
    }

    //根据展会id查询动态展商表
    public function getTabName($exhibition_id)
    {
        $result = db('exhibition')->where(['id' => $exhibition_id])->find();
        $tabName = "";
        if ($result) {
            $tabName = $result['create_tablename'];
        } else {
            return json(['code' => 0, 'msg' => '缺少关键字段', 'data' => '']);
        }
        return $tabName;
    }


    /**
     * 预登记观众列表  0527
     * @return \think\response\Json|void
     *  // zh_20200528fmr
     */

    public function index(){
        $page = $this->request->param('page');
        $page = max(1,intval($page));
        $pagesize =$this->request->param('pageSize')==""?20:$this->request->param('pageSize');
        $exhibition_id =$this->request->param('exhibition_id');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $tabName='crm_'.$tabName;
        $param=" and p.status>3 and p.label=1 ";
        $keyword =input('keyword');
        if (!empty($keyword)) {
            $param.=" and p.company_name like '%$keyword%' ";
        }
        $sql2="select p.* ,e.title as exhibition_name,l.company_label_id";
        $sql2.=" from ".$tabName." as p ";
        $sql2.=" left join crm_exhibition as e on e.id=$exhibition_id";
        $sql2.=" left join (SELECT max(id) as company_label_id,company_id FROM crm_company_label WHERE label='协会展商' GROUP BY company_id) AS l ON l.company_id=p.company_id1";
        $sql2.=" where 1=1 $param";
        $sql2.=" order by p.id desc";
//        var_dump($sql2);die();
        $sql2.=" limit ".($page-1)*$pagesize.",$pagesize";

        $list=Db::query($sql2);
        $sql21=" select count(p.id) as count1 ";
        $sql21.=" from ".$tabName." as p where 1=1 $param";
        $total1=Db::query($sql21);
        $total=$total1[0]['count1']; //st
        foreach ($list as &$vv){
            $vv['country_chukou']=$this->is_serialized($vv['country_chukou'])?unserialize($vv['country_chukou']):array();
            $vv['doselfbrander_info']=$this->is_serialized($vv['doselfbrander_info'])?unserialize($vv['doselfbrander_info']):array();
            $bSql="select * from crm_booth where id in (select id_zwei from crm_contract where id_zhui=".$exhibition_id." and id_zs=".$vv['id']." and is_delete=0) and is_delete=0";
            $vv['both_list']=Db::query($bSql);
//            $vv['mobile_person']=$vv['mobile_person']?substr_replace($vv['mobile_person'], '****', 3, 4):'';
           $vv['mobile']=$vv['mobile']?substr_replace($vv['mobile'], '****', 3, 4):'';
        }
        return json(['code'=>1,'msg'=>'success','data'=>['list'=>$list,'count'=>$total]]);
    }

    public function is_serialized( $data ) {
        $data = trim( $data );

        if ( 'N;' == $data )

            return true;

        if ( !preg_match( '/^([adObis]):/', $data, $badions ) )

            return false;

        switch ( $badions[1] ) {
            case 'a' :

            case 'O' :

            case 's' :

                if ( preg_match( "/^{$badions[1]}:[0-9]+:.*[;}]\$/s", $data ) )

                    return true;

                break;

            case 'b' :

            case 'i' :

            case 'd' :

                if ( preg_match( "/^{$badions[1]}:[0-9.E-]+;\$/", $data ) )

                    return true;

                break;

        }

        return false;

    }

    //审核
    public function examine(){
        $exhibition_id =$this->request->param('exhibition_id');
        $ids=$this->request->param('ids');
        $types=$this->request->param('types');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'展会数据有误','data'=>'']);
        }
        if(empty($ids)){
            return json(['code'=>0,'msg'=>'观众数据有误','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $person_id = explode(',',$ids);
        foreach ($person_id as &$v){
            $res=db($tabName)->where(['id'=>$v])->find();
            if($res && $res['examine_type']!=1){
                $res=db($tabName)->where(['id'=>$v])->update(['examine_type'=>$types]);
            }
        }
        unset($v);
        return json(['code'=>1,'msg'=>'操作成功','data'=>'']);
    }


    // 导出  stj ce
    public function export()
    {
        $exhibition_id =$this->request->param('exhibition_id');
        $ids=$this->request->param('ids');
        $keyword =input('keyword');
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabNames=$this->getTabName($exhibition_id);
        $tabName='crm_'.$tabNames;
        $param=" and p.status>3 and p.account is NOT null and p.pwd is not null ";
        if(empty($ids)){
            if (!empty($keyword)) {
                $param.=" and p.company_name like '%$keyword%' ";
            }
        }else {
            $param.=" and p.id in ($ids)";
        }
        $sql2="select p.* ,e.title as exhibition_name,z.number as both_num,z.area as both_area,z.hall_id,h.title as hall_title,a.realname,l.company_label_title";
        $sql2.=" from ".$tabName." as p ";
        $sql2.=" left join crm_exhibition as e on e.id=$exhibition_id";
        $sql2.=" left join (SELECT max(id) number,agent_id,area,hall_id FROM crm_booth where is_delete=0 and exhibition_id=".$exhibition_id." GROUP BY agent_id) AS z ON z.agent_id=p.id";
        $sql2.=" left join crm_hall as h on z.hall_id=h.id";
        $sql2.=" left join crm_admin as a on a.id=p.member_id";
        $sql2.=" left join (SELECT max(id) as company_label_id,company_id,label as company_label_title FROM crm_company_label WHERE label='协会展商' GROUP BY company_id) AS l ON l.company_id=p.company_id1";
        $sql2.=" where 1=1 $param  AND p.label=1";
        $sql2.=" order by p.id desc";
        $list=Db::query($sql2);
        $columns = array(
            array('title' => '公司中文名', 'field' => 'company_name', 'width' => 12),
            array('title' => '公司英文文名', 'field' => 'company_name_en', 'width' => 12),
            array('title' => '中文简介', 'field' => 'company_describe', 'width' => 12),
            array('title' => '公司简介英文', 'field' => 'company_describe_en', 'width' => 12),
            array('title' => '一级分类', 'field' => 'category_id', 'width' => 12),
            array('title' => '二级分类', 'field' => 'second_category_id', 'width' => 12),
//            array('title' => '产品', 'field' => 'product', 'width' => 12),
//            array('title' => '产品介绍', 'field' => 'product_describe', 'width' => 12),
//            array('title' => '英文产品介绍', 'field' => 'product_describe_cn', 'width' => 12),
            array('title' => '是否做过自有品牌', 'field' => 'isdoselfbranderStr', 'width' => 12),
            array('title' => '合作公司', 'field' => 'isdoselfbranderBus', 'width' => 12),
            array('title' => '是否出口', 'field' => 'is_chukou_str', 'width' => 12),
            array('title' => '出口国家', 'field' => 'country_chukou_str', 'width' => 12),
            array('title' => '是否获取省级证书', 'field' => 'is_card_sheng_str', 'width' => 12),
            array('title' => '证书信息', 'field' => 'info_card_sheng', 'width' => 12),
            array('title' => '是否获得质量体系认证', 'field' => 'quality_system_str', 'width' => 12),
            array('title' => '是否协会会员', 'field' => 'company_label_title', 'width' => 12),
            array('title' => '是否一键代发', 'field' => 'Issue_on_behalf_str', 'width' => 12),
            array('title' => '体系认证信息', 'field' => 'quality_system_zheng', 'width' => 12),
            array('title' => '国家', 'field' => 'country', 'width' => 12),
            array('title' => '省份', 'field' => 'province', 'width' => 24),
            array('title' => '城市', 'field' => 'area_city', 'width' => 24),
            array('title' => '详细地址', 'field' => 'company_address', 'width' => 24),
            array('title' => '英文详细地址', 'field' => 'company_address_en', 'width' => 24),
            array('title' => '中文名', 'field' => 'name_cn', 'width' => 12),
            array('title' => '英文名', 'field' => 'name_en', 'width' => 12),
            array('title' => '手机', 'field' => 'mobile', 'width' => 24),
            array('title' => '电话', 'field' => 'tel', 'width' => 24),
            array('title' => '传真', 'field' => 'fax_person', 'width' => 24),
            array('title' => '邮件', 'field' => 'email', 'width' => 24),
            array('title' => '网址', 'field' => 'link', 'width' => 24),
            array('title' => '所属展会', 'field' => 'exhibition_name', 'width' => 24),
            array('title' => '展厅', 'field' => 'hall_title', 'width' => 24),
            array('title' => '展位号', 'field' => 'both_num', 'width' => 24),
            array('title' => '展位面积', 'field' => 'both_area', 'width' => 24),
            array('title' => '销售员', 'field' => 'realname', 'width' => 24),
            array('title' => 'logo', 'field' => 'logo', 'width' => 24),
            array('title' => '可编辑logo', 'field' => 'edit_logo', 'width' => 24),
            array('title' => '创建时间', 'field' => 'create_time_str', 'width' => 24),
        );

        foreach ($list as &$vv){
            $vv['is_chukou_str']=$vv['is_chukou']==0?'否':'是';
            $vv['isdoselfbranderStr']=$vv['isdoselfbrander']==0?'否':'是';
            $vv['is_card_sheng_str']=$vv['is_card_sheng']==0?'否':'是';
            $vv['quality_system_str']=$vv['quality_system']==0?'否':'是';
            $vv['Issue_on_behalf_str']=$vv['quality_system']==0?'否':'是';
            $vv['logo']=$vv['logo']?'https://crmapi.plfasia.com/public/uploads/image/'.$vv['logo']:'';
            $vv['edit_logo']=$vv['edit_logo']?'https://crmapi.plfasia.com/public/uploads/image/'.$vv['edit_logo']:'';
            $vv['create_time_str']=date("Y-m-d H:i:s",$vv['create_time']);
            $bus=[];
            if($vv['country_chukou'] && is_array(unserialize($vv['country_chukou']))){
                $vv['country_chukou_str']=implode('.',unserialize($vv['country_chukou']));
            }else{
                $vv['country_chukou_str']="";
            }
            if($vv['doselfbrander_info'] && is_array(unserialize($vv['doselfbrander_info']))){
                $bus=array_column(unserialize($vv['doselfbrander_info']), 'compzyname');;
            }else{
                $vv['country_chukou_str']="";
            }
            $vv['isdoselfbranderBus']=implode('.',$bus);
        }
        unset($vv);
        $title = "会刊明细";
        $filename = $title . date('Y-m-d-H-i', time()) . ".xls";
        return json(['code'=>1,'msg'=>'操作成功','data'=>['columns'=>$columns,'filename'=>$filename,'list'=>$list]]);
    }


    public function saveExcel(){
        $filename=$this->request->param('filename');
        $exhibition_id =$this->request->param('exhibition_id');
        if(empty($filename)){
            return json(['code'=>0,'msg'=>'excel缺失，请重新上传','data'=>'']);
        }
        if(empty($exhibition_id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $filename = ROOT_PATH.'public/uploads/excel/'.$filename;
        $reader = PHPExcel_IOFactory::createReaderForFile($filename);
        $excel = $reader->load($filename, $encode = 'utf-8');
        $excel_array=$excel->getsheet(0)->toArray();  //转换为数组格式
        array_shift($excel_array);  //删除第一个数组(标题);
        if (!is_array($excel_array) || empty($excel_array)) {
            return false;
        } else {
            //循环读取数据
            foreach ($excel_array as $key => $value) {
                $list[$key]['id'] = (int) $value[0];//id
                $list[$key]['name_cn'] = (string) $value[1];//中文姓名
                $list[$key]['name_en'] = (string) $value[2]; //英文姓名
                $list[$key]['mobile_person'] = (string) $value[3]; //电话
                $list[$key]['email'] = (string) $value[4]; // 邮件
                $list[$key]['country'] = (string) $value[5]; // 国家
                $list[$key]['province'] = (string) $value[6]; // 省份
                $list[$key]['area_city'] = (string) $value[7]; // 城市
                $list[$key]['area'] = (string) $value[8]; // 详细地址
                $list[$key]['company_name'] = (string)$value[9]; // 公司名称
                $list[$key]['company_name_en'] = (string)$value[10]; // 英文名称
                $list[$key]['industrys'] = (string)$value[11]; // 行业
                $list[$key]['department_cn'] = (string)$value[12]; // 部门
                $list[$key]['positions_cn'] = (string)$value[13]; // 职位
            }
        }
        foreach ($list as &$v){
            if(!empty($v['id'])){
                $res=db($tabName)->where(['id'=>$v['id']])->find();
                if($res){
                    unset($v['id']);
                    $res=db($tabName)->where(['id'=>$v['id']])->update();
                }
            }

        }
        unset($v);
        return json(['code' => 1, 'msg' => '操作成功']);
    }


    //获取观众详细信息
    public function getUserDetail(){
        $id=$this->request->param('id');//企业库人员id
        $exhibition_id = $this->request->param('exhibition_id');
        $exhibition=db('exhibition')->where(['is_delete'=>0,'id'=>$exhibition_id])->find();
        if(!$exhibition){
            return json(['code'=>1,'msg'=>'展会信息错误']);
        }
        if(empty($id)){
            return json(['code'=>0,'msg'=>'缺少关键字段','data'=>'']);
        };
        $tabName=$this->getTabName($exhibition_id);
        $result=db($tabName)->where(['id'=>$id])->find();
        return json(['code'=>1,'msg'=>'请求成功','data'=>$result]);
    }


    //获取问卷调查数据
    public function getQuestion(){
        $exhibition_id = $this->request->param('exhibition_id');
        $type = $this->request->param('type');
        $exhibition=db('exhibition')->where(['is_delete'=>0,'id'=>$exhibition_id])->find();
        if(!$exhibition){
            return json(['code'=>0,'msg'=>'展会信息错误']);
        }
        $param=" and w.question_is_del=0 and w.exhibition_id=$exhibition_id ";
        if(!empty($type)){
            $param.=" and question_category=$type ";
        }
        $sql2="select w.*,c.id as exhibition_id,c.title ";
        $sql2.=" from crm_zs_question_wenda as w LEFT JOIN crm_exhibition as c ON w.exhibition_id=c.id where 1=1 $param";
        $sql2.=" order by w.question_category,w.question_sort ";
        $list=Db::query($sql2);
        if(!$list){
            return json(['code'=>0,'msg'=>'题库查询错误']);
        }
        foreach ($list as &$v){
            $v['question_content_sel']=json_decode($v['question_content_sel'],true);
        }
        unset($v);
        return json(['code'=>1,'msg'=>'操作成功','data'=>$list]);
        
    }


    //获取推荐人
    public function recommendList(){
        $id=$this->request->param('id');//人员id
        if(empty($id)){
            return json(['code'=>0,'msg'=>'主键错误']);
        }
        $exhibition_id = $this->request->param('exhibition_id');
        $exhibition=db('exhibition')->where(['is_delete'=>0,'id'=>$exhibition_id])->find();
        if(!$exhibition){
            return json(['code'=>0,'msg'=>'展会信息错误']);
        }
        $tabName=$this->getTabName($exhibition_id);
        $list=db($tabName)->where(['parent_id'=>$id])->select();
        return json(['code'=>1,'msg'=>'操作成功','data'=>$list]);
    }



}